﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Missing = System.Reflection.Missing;
using System.IO;


public partial class WebUserControl_ControlAllBCSales : System.Web.UI.UserControl
{
    ClassExecute.ClassExecute dt;
    static string error = "";
    
    string str = BCDataCenter.Configuration.CurrentConnectionString;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            getTTPP();
        }
    }
    private void getTTPP()
    {
        dt = new ClassExecute.ClassExecute();
        DataTable TTPP = dt.Get_Data_DataTable(str, ref error, "[GET_SITENAME_ALL]", CommandType.StoredProcedure);
        DropTTPP.DataSource = TTPP;
        DropTTPP.DataValueField = "Sitecode";
        DropTTPP.DataTextField = "sitename";
        DropTTPP.DataBind();

    }
    protected void btSave_Click(object sender, EventArgs e)
    {
        try
        {
            DataTable db = (DataTable)ViewState["db"];
            //KIỂM TRA FILE CÓ TỒN TẠI HAY KHÔNG,NẾU CÓ THÌ XÓA
            if (File.Exists(Server.MapPath("BaoCaoAllBCSales.xls")))
            {
                File.Delete(Server.MapPath("BaoCaoAllBCSales.xls"));
            }

            Excel.ApplicationClass xlApp;
            Excel.Worksheet xlSheet;
            Excel.Workbook xlBook;
            //doi tuong Trống để thêm  vào xlApp sau đó lưu lại sau
            object missValue = System.Reflection.Missing.Value;
            //khoi tao doi tuong Com Excel moi
            xlApp = new Excel.ApplicationClass();
            xlBook = xlApp.Workbooks.Add(missValue);
            //su dung Sheet dau tien de thao tac
            xlSheet = (Excel.Worksheet)xlBook.Worksheets.get_Item(1);
            //không cho hiện ứng dụng Excel lên để tránh gây đơ máy
            xlApp.Visible = false;
            int socot = db.Columns.Count;
            int sohang = db.Rows.Count;
            int i, j;

            //set thuoc tinh cho tieu de
            xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1").Merge(false);
            Excel.Range caption = xlSheet.get_Range("A1", Convert.ToChar(socot + 65) + "1");
            caption.Select();
            caption.FormulaR1C1 = "Báo cáo ALL BC Sales từ ngày 01/" + txtNgay.Text.Substring(3).ToString() + " tới ngày " + txtNgay.Text;
            //căn lề cho tiêu đề
            caption.HorizontalAlignment = Excel.Constants.xlCenter;
            caption.Font.Bold = true;
            caption.VerticalAlignment = Excel.Constants.xlCenter;
            caption.Font.Size = 15;
            //màu nền cho tiêu đề
            caption.Interior.ColorIndex = 20;
            caption.RowHeight = 30;



            //set thuoc tinh cho cac header
            Excel.Range header = xlSheet.get_Range("A3", Convert.ToChar(socot + 65) + "2");
            header.Select();

            header.HorizontalAlignment = Excel.Constants.xlCenter;
            header.Font.Bold = true;
            header.Font.Size = 10;
            //điền tiêu đề cho các cột trong file excel
            for (i = 0; i < socot; i++)
                xlSheet.Cells[3, i + 2] = GridView1.Columns[i].HeaderText;
            //dien cot stt
            xlSheet.Cells[3, 1] = "STT";
            for (i = 0; i < sohang; i++)
                xlSheet.Cells[i + 4, 1] = i + 1;
            //dien du lieu vao sheet


            for (i = 0; i < sohang; i++)
                for (j = 0; j < socot; j++)
                {
                    xlSheet.Cells[i + 4, j + 2] = db.Rows[i][j];

                }
            //autofit độ rộng cho các cột
            for (i = 0; i < sohang; i++)
                ((Excel.Range)xlSheet.Cells[1, i + 1]).EntireColumn.AutoFit();

            //save file
            xlBook.SaveAs(Server.MapPath("BaoCaoAllBCSales.xls"), Excel.XlFileFormat.xlWorkbookNormal, missValue, missValue, missValue, missValue, Excel.XlSaveAsAccessMode.xlExclusive, missValue, missValue, missValue, missValue, missValue);
            xlBook.Close(true, missValue, missValue);
            xlApp.Quit();
            //tai file 
            saveFile(this.Response, Server.MapPath("BaoCaoAllBCSales.xls"));
            // release cac doi tuong COM
            releaseObject(xlSheet);
            releaseObject(xlBook);
            releaseObject(xlApp);
        }
       
        catch (Exception ex)
        {
            BCDataCenter.ClassWriteLogErr ob = new BCDataCenter.ClassWriteLogErr();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            ob.ErrorLog(ex.Message, method);
        }
       
    }
    protected void btXem_Click(object sender, EventArgs e)
    {
        BindGrid();
    }
    void BindGrid()
    {
        try
        {
            ViewState["db"] = null;
            dt = new ClassExecute.ClassExecute();
           DataTable  db = dt.Get_Data_DataTable(str, ref error, "[ls_generateAllBCSalesReport]", CommandType.StoredProcedure,
                new SqlParameter("@username", Session["username"].ToString()),
                new SqlParameter("@toDate", txtNgay.Text.Trim()),
                new SqlParameter("@siteCode", DropTTPP.SelectedValue.ToString()));
           ViewState["db"] = db;
            if (db.Rows.Count > 0)
            {
                GridView1.DataSource = db;
                GridView1.DataBind();
            }
        }
        catch (Exception ex)
        {
            BCDataCenter.ClassWriteLogErr obj = new BCDataCenter.ClassWriteLogErr();
            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            obj.ErrorLog(ex.Message, method);
        }
    }
    static public void releaseObject(object obj)
    {
        try
        {
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
            obj = null;
        }
        catch (Exception ex)
        {
            obj = null;
            throw new Exception("Exception Occured while releasing object " + ex.ToString());
        }
        finally
        {
            GC.Collect();
        }
    }
    void saveFile(System.Web.HttpResponse page, string PathfileName)
    {
        string filename = PathfileName;
        if (filename != "")
        {
            string path = filename;
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                page.Clear();
                page.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                page.AddHeader("Content-Length", file.Length.ToString());
                page.ContentType = "application/octet-stream";
                page.WriteFile(file.FullName);
                page.End();

            }
        }

    }
}
